import os
import requests
import pandas as pd
import shutil
import xlsxwriter
import xlrd
import numpy as np

#下载文件
def Savepics(img_urls,titles):
    for i in range(len(img_urls)):
        img_url=img_urls[i]
        title=titles[i]
        img_data=requests.get(img_url).content
        with open(str(title)+'.png','wb') as f:#with:当不存在时创建,存在时不创建
            f.write(img_data)
#数据写入
def Write_Data():
    book_data=pd.read_csv('result.csv',usecols=['titles','authors','ratings','details'],na_values='Null')
    titles=book_data['titles']
    authors = book_data['authors']
    ratings = book_data['ratings']
    details = book_data['details']

    workbook = xlsxwriter.Workbook('Book.xlsx',{'nan_inf_to_errors':True})
    worksheet = workbook.add_worksheet('豆瓣新书')

    nums=len(titles)

    worksheet.write(0,0,'图书封面')
    worksheet.write(0,1,'图书标题')
    worksheet.write(0,2,'图书作者')
    worksheet.write(0, 3, '图书评价')
    worksheet.write(0, 4, '图书细节')

    worksheet.set_column('A:A',20)
    worksheet.set_column('B:B', 20)
    worksheet.set_column('C:C', 20)
    worksheet.set_column('D:D', 20)
    worksheet.set_column('E:E', 20)
    #2：nums>10时,会出错？
    for i in range(1,30):
        worksheet.insert_image(i,0,titles[i]+'.png',{'x_scare':0.1,'y_scare':0.1})
        worksheet.write(i,1,titles[i])
        worksheet.write(i, 2, authors[i])
        worksheet.write(i, 3, ratings[i])
        worksheet.write(i, 4, details[i])

    workbook.close()

#读出数据
def Read_Data():
    df=pd.read_excel('Book.xlsx',sheet_name='豆瓣新书')
    print(df)
'''
    #1读取图片？
    path = 'C:\\Users\\SHINEION\\PycharmProjects\\First_Test\\Myxlsxdata/Book.xlsx'
    from PIL import ImageGrab
    import win32com.client as win32

    excel = win32.gencache.EnsureDispatch('Excel.Application')
    workbook = excel.Workbooks.Open(path)

    for sheet in workbook.Worksheets:
        for i, shape in enumerate(sheet.Shapes):
            if shape.Name.startswith('Picture'):
                shape.Copy()
                image = ImageGrab.grabclipboard()
                print(image)
                image.save('{}.png'.format(i + 1), 'png')
    excel.Quit()
    '''

if __name__ == '__main__':

    if 'Myxlsxdata' not in os.listdir():
        os.mkdir('Myxlsxdata')
    #if not os.path.exists('文件名路径')
        shutil.copy('result.csv','Myxlsxdata/result.csv')
    os.chdir('Myxlsxdata')
    book_data=pd.read_csv('result.csv')#读入爬取文件
    img_urls=book_data['img_urls']
    titles=book_data['titles']
    Savepics(img_urls,titles)
    Write_Data()

    Read_Data()